package com.tidu.service;

import com.tidu.entry.DataSourceMetadata;
import com.tidu.entry.ESDocument;
import com.tidu.manager.DataSourceManager;
import com.tidu.manager.MetadataRepository;
import com.tidu.utils.TableSchemaAnalyzer;
import lombok.RequiredArgsConstructor;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.util.*;
import java.util.stream.Collectors;

@Service
@RequiredArgsConstructor
public class DataSyncService {

    private final MetadataRepository metadataRepository;
    private final DataSourceManager dataSourceManager;
    private final ElasticsearchService elasticsearchService;

    public void syncAllDataSources() {
        List<DataSourceMetadata> metadataList = metadataRepository.getAllActiveMetadata();
        metadataList = metadataList.stream().filter(metadata -> metadata.getDbType().equals("mysql")).collect(Collectors.toList());

        for (DataSourceMetadata metadata : metadataList) {
            try {
                syncDataSource(metadata);
            } catch (Exception e) {
                System.err.println("同步数据源失败 [" + metadata.getSourceName() + "]: " + e.getMessage());
                e.printStackTrace();
            }
        }
    }

    private void syncDataSource(DataSourceMetadata metadata) throws Exception {
        System.out.println("正在同步数据源: " + metadata.getSourceName());

        try (Connection conn = dataSourceManager.getConnection(metadata)) {
            String tableName = metadata.getTableName();

            // 获取表的总行数
            long totalRows = getTableRowCount(conn, tableName, metadata.getDbType());
            System.out.println("表 " + tableName + " 总行数: " + totalRows);

            // 获取需要查询的列（主键列 + 搜索列）
            Set<String> searchColumns = TableSchemaAnalyzer.getSearchColumns(conn, tableName, metadata.getDbType());
            // 使用TableSchemaAnalyzer获取主键列
            String primaryKeyColumn = TableSchemaAnalyzer.getPrimaryKeyColumn(conn, tableName, metadata.getDbType());

            // 合并主键列和搜索列
            Set<String> requiredColumns = new HashSet<>(searchColumns);
            if (primaryKeyColumn != null) {
                requiredColumns.add(primaryKeyColumn);
            }

            int pageSize = 1000;
            long offset = 0;

            while (offset < totalRows) {
                // 使用对应数据库的分页查询方式
                List<ESDocument> documents = fetchPageData(conn, tableName, offset, pageSize, metadata, requiredColumns);

                if (!documents.isEmpty()) {
                    // 批量保存到ES
                    elasticsearchService.saveDocuments(tableName, documents);
                    System.out.println("已同步 " + documents.size() + " 条记录，偏移量: " + offset);
                }

                offset += pageSize;

                // 避免请求过于频繁
                Thread.sleep(100);
            }
        }
    }

    private long getTableRowCount(Connection conn, String tableName, String dbType) throws SQLException {
        String countSql;
        switch (dbType.toLowerCase()) {
            case "oracle":
                countSql = "SELECT COUNT(*) FROM \"" + tableName.toUpperCase() + "\"";
                break;
            case "postgresql":
                countSql = "SELECT COUNT(*) FROM \"" + tableName.toLowerCase() + "\"";
                break;
            case "sqlserver":
                countSql = "SELECT COUNT(*) FROM [" + tableName + "]";
                break;
            default: // MySQL等
                countSql = "SELECT COUNT(*) FROM `" + tableName + "`";
                break;
        }

        try (PreparedStatement stmt = conn.prepareStatement(countSql);
             ResultSet rs = stmt.executeQuery()) {
            if (rs.next()) {
                return rs.getLong(1);
            }
        }
        return 0;
    }

    private List<ESDocument> fetchPageData(Connection conn, String tableName, long offset, int pageSize,
                                           DataSourceMetadata metadata, Set<String> requiredColumns) throws Exception {
        String pagingSql = generatePagingQueryWithColumns(tableName, offset, pageSize, metadata.getDbType(), requiredColumns);

        List<ESDocument> documents = new ArrayList<>();

        try (PreparedStatement stmt = conn.prepareStatement(pagingSql);
             ResultSet rs = stmt.executeQuery()) {

            // 获取列信息
            ResultSetMetaData rsMetaData = rs.getMetaData();
            int columnCount = rsMetaData.getColumnCount();
            List<String> columnNames = new ArrayList<>();

            for (int i = 1; i <= columnCount; i++) {
                columnNames.add(rsMetaData.getColumnName(i));
            }

            // 确定主键列（使用TableSchemaAnalyzer）
            String primaryKeyColumn = TableSchemaAnalyzer.getPrimaryKeyColumn(conn, tableName, metadata.getDbType());
            if (primaryKeyColumn == null || !columnNames.contains(primaryKeyColumn)) {
                // 如果找不到主键，则使用第一列作为标识符
                primaryKeyColumn = columnNames.get(0);
            }

            while (rs.next()) {
                Map<String, Object> contentFields = new HashMap<>();
                String rowId = null;

                for (String columnName : columnNames) {
                    Object value = rs.getObject(columnName);
                    contentFields.put(columnName, value);

                    if (columnName.equalsIgnoreCase(primaryKeyColumn)) {
                        rowId = value != null ? value.toString() : UUID.randomUUID().toString();
                    }
                }

                // 如果没有找到合适的rowId，则生成一个UUID
                if (rowId == null) {
                    rowId = UUID.randomUUID().toString();
                }

                // 构建组合文本用于全文搜索（仅使用需要的字段）
                String combinedText = contentFields.entrySet().stream()
                        .filter(entry -> requiredColumns.contains(entry.getKey()))
                        .map(entry -> entry.getValue() != null ? entry.getValue().toString() : "")
                        .collect(Collectors.joining(" "));

                ESDocument document = new ESDocument(
                        tableName,
                        rowId,
                        (long) metadata.getId(),
                        contentFields,
                        combinedText
                );

                documents.add(document);
            }
        }

        return documents;
    }

    private String generatePagingQueryWithColumns(String tableName, long offset, int pageSize, String dbType, Set<String> columns) {
        // 构建列名列表
        String columnList;
        if (columns.isEmpty()) {
            columnList = "*";
        } else {
            switch (dbType.toLowerCase()) {
                case "oracle":
                    columnList = columns.stream()
                            .map(col -> "\"" + col.toUpperCase() + "\"")
                            .collect(Collectors.joining(", "));
                    break;
                case "postgresql":
                    columnList = columns.stream()
                            .map(col -> "\"" + col.toLowerCase() + "\"")
                            .collect(Collectors.joining(", "));
                    break;
                case "sqlserver":
                    columnList = columns.stream()
                            .map(col -> "[" + col + "]")
                            .collect(Collectors.joining(", "));
                    break;
                default: // MySQL等
                    columnList = columns.stream()
                            .map(col -> "`" + col + "`")
                            .collect(Collectors.joining(", "));
                    break;
            }
        }

        // 生成基础查询语句
        String baseQuery;
        switch (dbType.toLowerCase()) {
            case "oracle":
                baseQuery = String.format("SELECT %s FROM \"%s\"", columnList, tableName.toUpperCase());
                break;
            case "postgresql":
                baseQuery = String.format("SELECT %s FROM \"%s\"", columnList, tableName.toLowerCase());
                break;
            case "sqlserver":
                baseQuery = String.format("SELECT %s FROM [%s]", columnList, tableName);
                break;
            default: // MySQL等
                baseQuery = String.format("SELECT %s FROM `%s`", columnList, tableName);
                break;
        }

        // 添加分页
        switch (dbType.toLowerCase()) {
            case "mysql":
                return String.format("%s LIMIT %d OFFSET %d", baseQuery, pageSize, offset);
            case "oracle":
                long endRow = offset + pageSize;
                return String.format("SELECT * FROM (SELECT t.*, ROWNUM rn FROM (%s) t WHERE ROWNUM <= %d) WHERE rn > %d",
                        baseQuery, endRow, offset);
            case "postgresql":
                return String.format("%s LIMIT %d OFFSET %d", baseQuery, pageSize, offset);
            case "sqlserver":
                return String.format("%s ORDER BY (SELECT NULL) OFFSET %d ROWS FETCH NEXT %d ROWS ONLY",
                        baseQuery, offset, pageSize);
            default:
                throw new IllegalArgumentException("不支持的数据库类型: " + dbType);
        }
    }
}
